﻿//====================================================================
// Create by Daniel
// file： YCCategory.cs
// project ：YunCMS
// Build time：2012-09-09
// modify:
// description:
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using YunCMS.Model;
using YunCMS.IDAL;

namespace YunCMS.DAL
{
    /// <summary>
    /// Data Access Layer of dbo.YC_Category.
    /// </summary>
    public partial class YCCategoryDAL : IYCCategoryDAL
    {
        #region

        /// <summary>
        /// constractor
        /// </summary>
        public YCCategoryDAL()
        {
        }
        #endregion

        #region -----------Methods-----------
        /// <summary>
        /// Insert a model to Database
        /// </summary>
        /// <param name="model">YCCategorynew model</param>
        /// <returns>The new item Id</returns>
        public int Add(YCCategory model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into YC_Category(");
            sqlStr.Append("ParentID,Title,ModuleType,Active,Target,Description,Content,NodeLevel,OrderNumber,DisplayType,Access,ImageUrl,Keywords,Folder,FilePath,OuterLink,NodePath)");
            sqlStr.Append(" values (");
            sqlStr.Append("@ParentID,@Title,@ModuleType,@Active,@Target,@Description,@Content,@NodeLevel,@OrderNumber,@DisplayType,@Access,@ImageUrl,@Keywords,@Folder,@FilePath,@OuterLink,@NodePath)");
            sqlStr.Append(";select @@IDENTITY");
            int res;

            res = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, false));
            return res;
        }
        /// <summary>
        /// Insert a model to Database in transaction.
        /// </summary>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="model">YCCategorynew model</param>
        /// <returns>The new item Id</returns>
        public int Add(SqlTransaction trans, YCCategory model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into YC_Category(");
            sqlStr.Append("ParentID,Title,ModuleType,Active,Target,Description,Content,NodeLevel,OrderNumber,DisplayType,Access,ImageUrl,Keywords,Folder,FilePath,OuterLink,NodePath)");
            sqlStr.Append(" values (");
            sqlStr.Append("@ParentID,@Title,@ModuleType,@Active,@Target,@Description,@Content,@NodeLevel,@OrderNumber,@DisplayType,@Access,@ImageUrl,@Keywords,@Folder,@FilePath,@OuterLink,@NodePath)");
            int res;

            res = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, false));
            return res;
        }
        /// <summary>
        /// update a model to db
        /// </summary>
        /// <param name="model">YCCategory</param>
        /// <returns>rows affected</returns>
        public int Update(YCCategory model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update YC_Category set ");
            sqlStr.Append("ParentID=@ParentID,Title=@Title,ModuleType=@ModuleType,Active=@Active,Target=@Target,Description=@Description,Content=@Content,NodeLevel=@NodeLevel,OrderNumber=@OrderNumber,DisplayType=@DisplayType,Access=@Access,ImageUrl=@ImageUrl,Keywords=@Keywords,Folder=@Folder,FilePath=@FilePath,OuterLink=@OuterLink,NodePath=@NodePath");
            sqlStr.Append(" where ID=@ID");
            int res;

            res = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, true));
            return res;
        }

        /// <summary>
        /// update a model to db in transaction
        /// </summary>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="model">YCCategory</param>
        /// <returns>rows affected</returns>
        public int Update(SqlTransaction trans, YCCategory model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update YC_Category set ");
            sqlStr.Append("ParentID=@ParentID,Title=@Title,ModuleType=@ModuleType,Active=@Active,Target=@Target,Description=@Description,Content=@Content,NodeLevel=@NodeLevel,OrderNumber=@OrderNumber,DisplayType=@DisplayType,Access=@Access,ImageUrl=@ImageUrl,Keywords=@Keywords,Folder=@Folder,FilePath=@FilePath,OuterLink=@OuterLink,NodePath=@NodePath");
            sqlStr.Append(" where ID=@ID");
            int res;

            res = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, true));
            return res;
        }

        /// <summary>
        /// delete a model from db
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns>rows affected</returns>
        public int Delete(int id)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete YC_Category ");
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.Int),
			
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), parameters);
        }

        /// <summary>
        /// delete a model from db in transaction
        /// </summary>        
        /// <param name="trans">SqlTransaction</param>
        /// <param name="id">ID</param>
        /// <returns>rows affected</returns>
        public int Delete(SqlTransaction trans, int id)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete YC_Category ");
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.Int),
			
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), parameters);
        }

        /// <summary>
        ///Get YCCategory Entity
        /// </summary>
        /// <param name="dr">dr</param>
        /// <returns>YCCategory Entity</returns>
        public YCCategory PopulateYCCategoryFromDr(IDataReader dr)
        {
            YCCategory Obj = new YCCategory();

            Obj.ID = Convert.ToInt32(dr["ID"]);
            Obj.ParentID = Convert.ToInt32(dr["ParentID"]);
            Obj.Title = dr["Title"].ToString();
            Obj.ModuleType = dr["ModuleType"].ToString();
            if ((dr["Active"]) == DBNull.Value) { Obj.Active = null; } else { Obj.Active = (bool)dr["Active"]; }
            Obj.Target = dr["Target"].ToString();
            Obj.Description = dr["Description"].ToString();
            Obj.Content = dr["Content"].ToString();
            if ((dr["NodeLevel"]) == DBNull.Value) { Obj.NodeLevel = null; } else { Obj.NodeLevel = Convert.ToInt32(dr["NodeLevel"]); }
            if ((dr["OrderNumber"]) == DBNull.Value) { Obj.OrderNumber = null; } else { Obj.OrderNumber = Convert.ToInt32(dr["OrderNumber"]); }
            if ((dr["DisplayType"]) == DBNull.Value) { Obj.DisplayType = null; } else { Obj.DisplayType = Convert.ToInt32(dr["DisplayType"]); }
            if ((dr["Access"]) == DBNull.Value) { Obj.Access = null; } else { Obj.Access = Convert.ToInt32(dr["Access"]); }
            Obj.ImageUrl = dr["ImageUrl"].ToString();
            Obj.Keywords = dr["Keywords"].ToString();
            Obj.Folder = dr["Folder"].ToString();
            Obj.FilePath = dr["FilePath"].ToString();
            Obj.OuterLink = dr["OuterLink"].ToString();
            Obj.NodePath = dr["NodePath"].ToString();

            return Obj;
        }

        /// <summary>
        /// get a YCCategory object 
        /// </summary>
        /// <param name=id>ID</param>
        /// <returns>YCCategory object</returns>
        public YCCategory GetYCCategory(int id)
        {
            YCCategory _obj = null;
            SqlParameter[] parameters ={
			    new SqlParameter("@ID",SqlDbType.Int)
			};
            parameters[0].Value = id;
            string sqlStr = "select * from YC_Category where ID=@ID";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters))
            {
                if (dr.Read())
                {
                    _obj = PopulateYCCategoryFromDr(dr);
                }
            }
            return _obj;
        }

        public IList<YCCategory> GetCategoryList(string strwhere)
        {
            IList<YCCategory> Obj = new List<YCCategory>();
            SqlDataReader dr;

            string sqlStr = "select * from YC_Category";

            if (!string.IsNullOrEmpty(strwhere))
            {
                sqlStr += " where 1=1 " + strwhere;
            }
            dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr);
            using (dr)
            {
                while (dr.Read())
                {
                    Obj.Add(PopulateYCCategoryFromDr(dr));
                }
            }
            return Obj;

        }
        /// <summary>
        /// Get YCCategorys List
        /// </summary>
        /// <returns>List</returns>
        public IList<YCCategory> GetList(string moduleType)
        {
            IList<YCCategory> Obj = new List<YCCategory>();
            SqlDataReader dr;

            string sqlStr = "select * from YC_Category";

            if (!string.IsNullOrEmpty(moduleType))
            {
                sqlStr += " where ModuleType=@ModuleType";
                SqlParameter[] parameters ={
			        new SqlParameter("@ModuleType",SqlDbType.VarChar)
			    };
                parameters[0].Value = moduleType;
                dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters);
            }
            else
            {
                dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr);
            }

            using (dr)
            {
                while (dr.Read())
                {
                    Obj.Add(PopulateYCCategoryFromDr(dr));
                }
            }
            return Obj;
        }

        public int UpdateOneField(int id, string fieldName, object value)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update YC_Category set ");
            sqlStr.Append(fieldName + "=@fieldValue");
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.Int),
			                new SqlParameter("@fieldValue",value)
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), parameters);
        }


        /// <summary>
        /// if exist a YCCategory according by ID
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>true/false</returns>
        public bool IsExist(int id)
        {
            SqlParameter[] parameters ={
                                      new SqlParameter("@ID",SqlDbType.Int)
                                  };
            parameters[0].Value = id;
            string sqlStr = "select Count(1) from YC_Category where ID=@ID";
            int a = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sqlStr, parameters));
            if (a > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private SqlParameter[] PrepareSqlParams(YCCategory model, bool isUpdate)
        {
            List<SqlParameter> parameters = new List<SqlParameter>() {							
							new SqlParameter("@ParentID",SqlDbType.Int),
							new SqlParameter("@Title",SqlDbType.NVarChar),
							new SqlParameter("@ModuleType",SqlDbType.VarChar),
							new SqlParameter("@Active",SqlDbType.Bit),
							new SqlParameter("@Target",SqlDbType.VarChar),
							new SqlParameter("@Description",SqlDbType.NVarChar),
							new SqlParameter("@Content",SqlDbType.NText),
							new SqlParameter("@NodeLevel",SqlDbType.Int),
							new SqlParameter("@OrderNumber",SqlDbType.Int),
							new SqlParameter("@DisplayType",SqlDbType.Int),
							new SqlParameter("@Access",SqlDbType.Int),
							new SqlParameter("@ImageUrl",SqlDbType.NVarChar),
							new SqlParameter("@Keywords",SqlDbType.NVarChar),
							new SqlParameter("@Folder",SqlDbType.NVarChar),
							new SqlParameter("@FilePath",SqlDbType.NVarChar),
							new SqlParameter("@OuterLink",SqlDbType.NVarChar),
							new SqlParameter("@NodePath",SqlDbType.VarChar)
			};
            parameters[0].Value = model.ParentID;
            if (model.Title != String.Empty)
                parameters[1].Value = model.Title;
            else
                parameters[1].Value = DBNull.Value;
            if (model.ModuleType != String.Empty)
                parameters[2].Value = model.ModuleType;
            else
                parameters[2].Value = DBNull.Value;
            if (model.Active != null)
                parameters[3].Value = model.Active;
            else
                parameters[3].Value = DBNull.Value;
            if (model.Target != String.Empty)
                parameters[4].Value = model.Target;
            else
                parameters[4].Value = DBNull.Value;
            if (model.Description != String.Empty)
                parameters[5].Value = model.Description;
            else
                parameters[5].Value = DBNull.Value;
            if (model.Content != String.Empty)
                parameters[6].Value = model.Content;
            else
                parameters[6].Value = DBNull.Value;
            if (model.NodeLevel != null)
                parameters[7].Value = model.NodeLevel;
            else
                parameters[7].Value = DBNull.Value;
            if (model.OrderNumber != null)
                parameters[8].Value = model.OrderNumber;
            else
                parameters[8].Value = DBNull.Value;
            if (model.DisplayType != null)
                parameters[9].Value = model.DisplayType;
            else
                parameters[9].Value = DBNull.Value;
            if (model.Access != null)
                parameters[10].Value = model.Access;
            else
                parameters[10].Value = DBNull.Value;
            if (model.ImageUrl != String.Empty)
                parameters[11].Value = model.ImageUrl;
            else
                parameters[11].Value = DBNull.Value;
            if (model.Keywords != String.Empty)
                parameters[12].Value = model.Keywords;
            else
                parameters[12].Value = DBNull.Value;
            if (model.Folder != String.Empty)
                parameters[13].Value = model.Folder;
            else
                parameters[13].Value = DBNull.Value;
            if (model.FilePath != String.Empty)
                parameters[14].Value = model.FilePath;
            else
                parameters[14].Value = DBNull.Value;
            if (model.OuterLink != String.Empty)
                parameters[15].Value = model.OuterLink;
            else
                parameters[15].Value = DBNull.Value;
            if (model.NodePath != String.Empty)
                parameters[16].Value = model.NodePath;
            else
                parameters[16].Value = DBNull.Value;

            if (isUpdate)
            {
                SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = model.ID;
                parameters.Add(param);
            }

            return parameters.ToArray();
        }

        /*
        public int GetMaxId()
        {
            int maxid;
            string sqlStr="select max(ID) from YC_Category order by ID desc";
            using(SqlDataReader dr=SqlHelper.ExecuteReader(CommandType.Text,sqlStr))
            {
                if(dr.Read())
                {
                    maxid = Convert.ToInt32( dr["ID"]);
                }
                else
                    maxid = 0;
            }
            return maxid;
        }

        */
        #endregion
		
    }
}




